﻿using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection.Emit;
using System.Threading.Tasks;
using YougeBookmark.Models;
using YougeBookmark.Utilities;

namespace YougeBookmark.Services
{
    /// <summary>
    /// 数据库服务核心类（使用EF Core + SQLite）
    /// 功能说明：
    /// 1. 管理数据库连接生命周期
    /// 2. 实现CRUD基础操作
    /// 3. 处理复杂查询和事务
    /// </summary>
    public class DatabaseService : IDisposable
    {
        private readonly BookmarkContext _context;
        private SqliteConnection _connection;
        private bool _disposed;

        /// <summary>
        /// 初始化数据库服务（自动创建/迁移数据库）
        /// </summary>
        public DatabaseService()
        {
            _connection = new SqliteConnection("Data Source=Data/Database/Bookmarks.db");
            _connection.Open();

            _context = new BookmarkContext(
                new DbContextOptionsBuilder<BookmarkContext>()
                    .UseSqlite(_connection)
                    .Options);

            // 自动应用迁移
            _context.Database.Migrate();
        }

        #region 书签操作
        /// <summary>
        /// 添加新书签（带事务处理）
        /// </summary>
        /// <param name="bookmark">书签实例</param>
        /// <param name="tagIds">关联标签ID集合</param>
        /// <returns>操作是否成功</returns>
        public async Task<bool> AddBookmarkAsync(Bookmark bookmark, IEnumerable<int> tagIds)
        {
            using var transaction = await _context.Database.BeginTransactionAsync();

            try
            {
                // 验证URL唯一性
                if (await _context.Bookmarks.AnyAsync(b => b.Url == bookmark.Url))
                    throw new DuplicateNameException("URL已存在");

                _context.Bookmarks.Add(bookmark);
                await _context.SaveChangesAsync();

                // 添加标签关联
                foreach (var tagId in tagIds.Distinct())
                {
                    var tag = await _context.Tags.FindAsync(tagId);
                    bookmark.AddTag(tag);
                }

                await _context.SaveChangesAsync();
                await transaction.CommitAsync();
                return true;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                Logger.Error(ex, "添加书签失败");
                return false;
            }
        }

        /// <summary>
        /// 分页查询书签（支持复杂条件）
        /// </summary>
        public async Task<(List<Bookmark> data, int total)> QueryBookmarksAsync(PaginationHelper helper)
        {
            try
            {
                helper.ValidateParameters();

                var (dataSql, countSql, parameters) = helper.BuildPagedQuery();
                var paramList = parameters.Select(p => new SqliteParameter(p.Key, p.Value));

                // 执行分页查询
                var data = await _context.Bookmarks
                    .FromSqlRaw(dataSql, paramList.ToArray())
                    .AsNoTracking()
                    .ToListAsync();

                // 获取总数（修正后的标量查询）
                var total = await _context.Set<ScalarResult<int>>()
                    .FromSqlRaw(countSql, paramList.ToArray())
                    .Select(s => s.Value)
                    .FirstOrDefaultAsync();

                return (data, total);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "查询书签失败");
                return (new List<Bookmark>(), 0);
            }
        }
        #endregion

        #region 标签管理
        /// <summary>
        /// 获取所有标签（带缓存机制）
        /// </summary>
        public async Task<List<Tag>> GetAllTagsAsync()
        {
            try
            {
                return await _context.Tags
                    .AsNoTracking()
                    .OrderBy(t => t.Name)
                    .ToListAsync();
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "获取标签失败");
                return new List<Tag>();
            }
        }

        /// <summary>
        /// 更新标签信息（并发检查）
        /// </summary>
        public async Task<bool> UpdateTagAsync(Tag updatedTag)
        {
            try
            {
                var existing = await _context.Tags
                    .FirstOrDefaultAsync(t => t.Id == updatedTag.Id);

                if (existing == null) return false;

                // 检查名称冲突
                if (await _context.Tags.AnyAsync(t =>
                    t.Name == updatedTag.Name && t.Id != updatedTag.Id))
                {
                    throw new DuplicateNameException("标签名称已存在");
                }

                _context.Entry(existing).CurrentValues.SetValues(updatedTag);
                return await _context.SaveChangesAsync() > 0;
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "更新标签失败");
                return false;
            }
        }
        #endregion

        #region 数据库管理
        /// <summary>
        /// 执行原始SQL命令（用于特殊操作）
        /// </summary>
        public async Task<int> ExecuteSqlAsync(string sql, params SqliteParameter[] parameters)
        {
            return await _context.Database.ExecuteSqlRawAsync(sql, parameters);
        }

        /// <summary>
        /// 备份数据库到指定路径
        /// </summary>
        public void BackupDatabase(string targetPath)
        {
            using var backup = new SqliteConnection($"Data Source={targetPath}");
            _connection.BackupDatabase(backup);
        }
        #endregion

        #region 资源释放
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;

            if (disposing)
            {
                _connection?.Close();
                _connection?.Dispose();
                _context?.Dispose();
            }
            _disposed = true;
        }
        #endregion
    }

    /// <summary>
    /// EF Core数据库上下文（内部类）
    /// </summary>
    internal class BookmarkContext : DbContext
    {
        public DbSet<Bookmark> Bookmarks { get; set; }
        public DbSet<Tag> Tags { get; set; }
        public DbSet<BookmarkTag> BookmarkTags { get; set; }

        public BookmarkContext(DbContextOptions options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // 配置复合主键
            modelBuilder.Entity<BookmarkTag>()
                .HasKey(bt => new { bt.BookmarkId, bt.TagId });

            // 配置索引
            modelBuilder.Entity<Bookmark>()
                .HasIndex(b => b.Url)
                .IsUnique();

            modelBuilder.Entity<Tag>()
                .HasIndex(t => t.Name)
                .IsUnique();

            // 配置级联删除
            modelBuilder.Entity<BookmarkTag>()
                .HasOne(bt => bt.Bookmark)
                .WithMany(b => b.BookmarkTags)
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<BookmarkTag>()
                .HasOne(bt => bt.Tag)
                .WithMany(t => t.BookmarkTags)
                .OnDelete(DeleteBehavior.Cascade);
        }
    }
}